package com.ar4j.bench.simplejdbc.service;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcDaoSupport;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import org.springframework.stereotype.Service;

import com.ar4j.bench.common.domain.IItem;
import com.ar4j.bench.common.domain.IProduct;
import com.ar4j.bench.common.domain.ProductWithItems;
import com.ar4j.bench.common.service.IProductService;
import com.ar4j.bench.jdbc.domain.Product;

/**
 * A product service implemented using spring's SimpleJdbcTemplate
 */
@Service
public class ProductSimpleJdbcService extends SimpleJdbcDaoSupport implements IProductService {
  private static final String PRODUCT_BY_ID = "SELECT ID, CATEGORY_ID, NAME, IMAGE, DESCRIPTION FROM PRODUCT WHERE ID = ?";
  private static final String PRODUCTS_BY_CATEGORY_ID = "SELECT ID, CATEGORY_ID, NAME, IMAGE, DESCRIPTION FROM PRODUCT WHERE CATEGORY_ID = ?";
  private static final String DELETE_BY_ID = "DELETE FROM PRODUCT WHERE ID = ?";
  private static final String UPDATE_SQL = "UPDATE PRODUCT SET CATEGORY_ID = :categoryId, NAME = :name, IMAGE = :image, DESCRIPTION = :description WHERE ID = :id";
  
  private static final RowMapper<Product> MAPPER = ParameterizedBeanPropertyRowMapper.newInstance(Product.class);
  
  @Autowired
  private ItemSimpleJdbcService itemService;
  
  private SimpleJdbcInsert insert;

  @Override
  public Long addProduct(IProduct product) {
    SqlParameterSource params = new BeanPropertySqlParameterSource(product);
    Number key = insert.executeAndReturnKey(params);
    return key.longValue();
  }

  @Override
  public List<IProduct> findByCategoryId(Long categoryId) {
    List<Product> products = getSimpleJdbcTemplate().query(PRODUCTS_BY_CATEGORY_ID, MAPPER, categoryId);
    return new ArrayList<IProduct>(products);
  }

  @Override
  public IProduct findById(Long id) {
    return getSimpleJdbcTemplate().queryForObject(PRODUCT_BY_ID, MAPPER, id);
  }

  @Override
  public void removeById(Long id) {
    getSimpleJdbcTemplate().update(DELETE_BY_ID, id);
  }

  @Override
  public void updateProduct(IProduct product) {
    SqlParameterSource params = new BeanPropertySqlParameterSource(product);
    getSimpleJdbcTemplate().update(UPDATE_SQL, params);
  }

  public Long addProductWithItems(ProductWithItems product) {
    Long productId = addProduct(product.getProduct());
    for(IItem item : product.getItems()) {
      item.setProductId(productId);
      itemService.addItem(item);
    }
    return productId;
  }

  public ProductWithItems findProductWithItemsById(Long id) {
    ProductWithItems out = new ProductWithItems();
    out.setProduct(findById(id));
    out.setItems(itemService.findByProductId(id));
    return out;
  }

  public List<ProductWithItems> findProductWithItemsByCategoryId(Long categoryId) {
    List<IProduct> products = findByCategoryId(categoryId);
    List<ProductWithItems> out = new ArrayList<ProductWithItems>();
    for(IProduct product : products) {
      ProductWithItems element = new ProductWithItems();
      element.setProduct(product);
      element.setItems(itemService.findByProductId(product.getId()));
      out.add(element);
    }
    
    return out;
  }

  public void updateProductWithItems(ProductWithItems product) {
    updateProduct(product.getProduct());
    
    Map<Long, IItem> fromDb = new HashMap<Long, IItem>();
    for(IItem item : itemService.findByProductId(product.getProduct().getId())) {
      fromDb.put(item.getId(), item);
    }
    
    for(IItem item : product.getItems()) {
      if(item.getId() == null || !fromDb.containsKey(item.getId())) {
        itemService.addItem(item);
      } else {
        itemService.updateItem(item);
        fromDb.remove(item.getId());
      }
    }
    
    for(IItem item : fromDb.values()) {
      itemService.removeById(item.getId());
    }
  }
  
  public void removeProductWithItemsById(Long id) {
    for(IItem item : itemService.findByProductId(id)) {
      itemService.removeById(item.getId());
    }
    
    removeById(id);
  }

  @Override
  protected void initDao() throws Exception {
    super.initDao();
    
    insert = new SimpleJdbcInsert(getDataSource())
      .withTableName("PRODUCT")
      .usingColumns("CATEGORY_ID", "NAME", "IMAGE", "DESCRIPTION")
      .usingGeneratedKeyColumns("ID");

  }

  @Autowired
  public void setAutowiredDataSource(DataSource dataSource) {
    setDataSource(dataSource);
  }

}
